package controllers;

import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.*;

import utils.UsersInfo;

import models.Location;
import models.PostQuestion;
import models.RatingBest;
import models.RatingNom;
import models.RatingRegion;
import models.ReplyQuestion;
import models.SolutionAdoption;
import models.SolutionSubmission;

import com.googlecode.objectify.Objectify;
import com.googlecode.objectify.ObjectifyService;
import com.googlecode.objectify.Query;

@SuppressWarnings("serial")
public class ajxExportBestOfRegionReportServlet extends HttpServlet {
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws IOException {
		resp.setContentType("application/vnd.ms-excel"); 		 
	    resp.setHeader("Content-Disposition", "attachment; filename=ExportBestofRegions.xls"); 
		PrintWriter out = resp.getWriter();


	//	out.println("<TABLE><TBODY><TR><TD width=100><SPAN class='lotusBtn lotusBtnAction lotusLeft'><A href='http://opscentral.asia.ad.celestica.com/global/collaboration/SavvySol.nsf/ExportBestofRegions?openagent'>Export XL</A></SPAN></TD></TR><TR><TD></TD></TR>");		
		out.println("<TR><TD style='FONT-FAMILY: Arial; COLOR: black; FONT-SIZE: 14px; FONT-WEIGHT: bold' colSpan=5><b>Best of Regions Report<b></TD></TR>");
		out.println("<TR><TD></TD></TR></TBODY></TABLE>");
		
		//tempQuery="year=" + tempval1 + "&benefit=" + tempval2 + "&location=" + tempval3 + "&customer=" + tempval4
		
		String syear = (String) req.getParameter("year");
		String sregion = (String) req.getParameter("region");
		
		Date today = new java.util.Date();
		SimpleDateFormat formatNowYear = new SimpleDateFormat("yyyy");
		SimpleDateFormat formatNowMonth = new SimpleDateFormat("MMMMM");
		String tYear = formatNowYear.format( today ); 
		String tMonth = formatNowMonth.format(today );
		
		
		if( syear!= null && syear.equals("0") ){
			syear = null;
		}
		if( sregion!=null && sregion.equals("0") ){
			sregion = null;
		}		

		Objectify ofy = ObjectifyService.begin();		
		Query<SolutionSubmission> q = null;
		boolean bYear, bRegion;
		bYear = bRegion = false;
		
		if( syear != null ){
			bYear = true;
		}		

		if( sregion != null ){
			bRegion = true;
		}
		
		
		q = ofy.query(SolutionSubmission.class);
		
		if ( bYear){
			q.filter("sYear", syear);
		}
		
		if( bRegion){
			
			if( sregion.equalsIgnoreCase("Best of Region") ){
				q.filter("bestOfRegion", true);
			}else if( sregion.equalsIgnoreCase("Best of Best") ){
				q.filter("bestOfBest", true);
			}else{
				q.filter("bestOfRegion", true);
			}
			
		}else{
			q.filter("bestOfRegion", true);
		}
		
		q.order("submittedON");

		out.println("<TABLE><TBODY><TR>");
		out.println("<TD class=style0 bgColor=#C0C0C0 width=100>Solution ID </TD>");
		out.println("<TD class=style0 bgColor=#C0C0C0 width=100>Solution Title </TD>");
		out.println("<TD class=style0 bgColor=#C0C0C0 width=100>Process Area </TD>");
		out.println("<TD class=style0 bgColor=#C0C0C0 width=100>Location </TD>");
		out.println("<TD class=style0 bgColor=#C0C0C0 width=100>Customer </TD>");
		out.println("<TD class=style2 bgColor=#99CC00 width=100>Adotion Count </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Expert Select </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Achived Benefit Category </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Achived Benefit Value </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Hard Savings ($USD)</TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Cost Avoidance($USD)</TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Oppotunity Cost($USD)</TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Shared Solution: Total Annual Production Savings ($USD) </TD>");
		out.println("<TD class=style2 bgColor=#99CC00 width=100>Adopted Solutions: Total Annual Production Savings ($USD) </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Total $ Saved </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Days to Implement Shared Solution </TD>");
		out.println("<TD class=style2 bgColor=#99CC00 width=100>Total Time Saved through Adoption</TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Implementation Date </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Date Shared (Posted Date) </TD>");
		out.println("<TD class=style1 bgColor=#FF9900 width=100>Quarter </TD></TR>");
		
		

		float total = 0;
		boolean hasData = false;
		float impDays = 0;
		String Quarter = "";
		
		for ( SolutionSubmission f : q.fetch() ) {
			
			out.println("<TR>");
			out.println("<TD class=style5 width=50>"+ f.getSolID() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getTitle() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getProcessArea() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getLocation() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getCustomer() +"</TD>");
			
			Query<SolutionAdoption> qq = ofy.query(SolutionAdoption.class).filter("solID", f.getSolID());
			out.println("<TD class=style5 width=50>"+ qq.countAll() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.isHasExpert() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getAchievedTypeDesc() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getAchievedValue() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getHardSavings() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getCostAvoidance() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getOppurtunityCost() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getAnnProdSavings() +"</TD>");
			total = f.getAnnProdSavings();
			hasData = false;
			impDays  = 0;
			for ( SolutionAdoption ff : qq.fetch() ) {
				out.println("<TD class=style5 width=50>"+ ff.getAnnProdSavings() +"</TD>");
				total = total + ff.getAnnProdSavings();
				impDays = impDays + ff.getImpDays();
				hasData = true;
			}
			
			if(!hasData){
				out.println("<TD class=style5 width=50>0</TD>");
			}
				
			out.println("<TD class=style5 width=50>"+ total +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getImpDays() +"</TD>");
			out.println("<TD class=style5 width=50>"+ impDays +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getDtImplemented() +"</TD>");
			out.println("<TD class=style5 width=50>"+ f.getSubmittedON() +"</TD>");
			
			Query<RatingRegion> qqq = null;
			Query<RatingBest> qqqq = null;
			Quarter = "";
			
			if( sregion!=null && sregion.equalsIgnoreCase("Best of Region") ){
				
				qqq = ofy.query(RatingRegion.class).filter("solID", f.getSolID());
				for ( RatingRegion x : qqq.fetch() ) {
					Quarter = x.getBestOfRegionQuarter();
				}
				
			}else if( sregion!=null && sregion.equalsIgnoreCase("Best of Best") ){
				qqqq = ofy.query(RatingBest.class).filter("solID", f.getSolID());
				for ( RatingBest y : qqqq.fetch() ) {
					Quarter = y.getBestOfBestQuater();
				}
				
			}else{
				qqq = ofy.query(RatingRegion.class).filter("solID", f.getSolID());
				for ( RatingRegion x : qqq.fetch() ) {
					Quarter = x.getBestOfRegionQuarter();
				}				
			}
			out.println("<TD class=style5 width=50>"+ Quarter +"</TD>");			

		}	
   		out.println("</TBODY></TABLE>");		
	}
}
